package com.leexuan.ordersystem.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.leexuan.ordersystem.beans.Deliver;

public class DeliverDB extends DB {

	private String ADD_DELIVER = "INSERT INTO deliver VALUES (?,?,?,?)";

	private String DEL_DELIVER = "DELETE FROM deliver WHERE did=?";

	private String GET_DELIVERS = "SELECT p.model,p.name,p.number,d.quantity,CONVERT(VARCHAR(30),D.date,111) as date FROM deliver AS d INNER JOIN orderdetail AS od ON d.odid=od.odid INNER JOIN product AS p On od.pid=p.pid  WHERE d.oid=? ORDER BY od.odid";

	public boolean addDeliver(Deliver st) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(ADD_DELIVER);

			pstmt.setString(1, st.getDate());
			pstmt.setInt(2, st.getOid());
			pstmt.setInt(3, st.getOdid());
			pstmt.setInt(4, st.getQuantity());

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public boolean delDeliver(int did) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(DEL_DELIVER);

			pstmt.setInt(1, did);

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public ArrayList getDeliverDetails(int oid) throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList delivers = new ArrayList();

		try {
			pstmt = conn.prepareStatement(GET_DELIVERS, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			pstmt.setInt(1, oid);

			rs = pstmt.executeQuery();

			while (rs.next()) {

				Deliver deliver = new Deliver();

				deliver.setModel(rs.getString("model"));
				deliver.setName(rs.getString("name"));
				deliver.setNumber(rs.getString("number"));
				deliver.setDate(rs.getString("date"));
				deliver.setQuantity(rs.getInt("quantity"));

				delivers.add(deliver);

			}

		} catch (SQLException ex) {
			ex.printStackTrace();

			throw new SQLException("SQLException on : DAO");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return delivers;
	}
}
